using System;
using System.Drawing;
using System.Data;
using System.Reflection;
using System.Collections;
using System.Threading;
using System.Windows.Forms;
using System.ComponentModel;
using System.Drawing.Imaging;
//using PCSComUtils.Common;
//using PCSComUtils.PCSExc;
//using PCSUtils.Log;
//using PCSUtils.Utils;
//using PCSUtils.Admin;
//using PCSComUtils.Admin.DS;
using System.Data.OleDb;
//using PCSUtils;
using Utils = PCSComUtils.DataAccess.Utils;
using System.Collections.Specialized;
using PCSUtils.Utils;
using C1.Win.C1Preview;
using C1.C1Report;
using C1.Win.C1Chart;
using Section = C1.C1Report.Section;

using System.IO;
using Microsoft.Office.Interop;
using Range = Microsoft.Office.Interop.Excel.Range;
using Excel = Microsoft.Office.Interop.Excel;



namespace VendorDeliveryAssessment
{
	/// <summary>
	/// <author>Thachnn</author>
	/// This report is a very complex report.
	/// It combines severals .NET DataTables with C1Report and Interop with Excel to get some chart images.
	/// 
	/// 
	/// --- The first 3 rows: using Sum() function of C1Report VBScript to calculate. Sum() function work rather well.
	/// 
	/// --- Auto generate the DayInMonth, DayOfWeek serries (by using 2 fuctions of mine from ReportBuilder)
	/// 
	/// --- In the detail section:
	/// There are many fields put here (about 200fields). Some info-fields like PartNo, PartName, Model. .... and a large pack of indexed fields (from 1 till 31).
	/// They are fldPlanxx, fldAdjxx, fldActualxx, fldReturnxx, fldProgressDayxx, fldProgressAccumulatexx and fldAssessmentxx.
	/// We do not spread these 200 fields vertically. We group 'em into 6 rows, 
	/// Plan, Adj and Actual, Return, ProgressDay field are fill from tables (built in C# code). 
	/// Other fields like , Progress Accumulate, Assessment was calculate on render time, 
	/// (by VBScript on the C1Report XML layout file)
	/// But if we render in render time by C1, there are some problems we can't hanlde, C1 is rather bad if we process too much field calculating.
	/// 
	/// Actual -- get from dtbActualTable
	/// REturn -- get from dtbReturnTable
	/// --- all indexed fields will be re-render, re-spread depending on the number of day in month ( using the Spread function of mine in the ReportBuilder)
	/// 
	/// --- There are some sumRow() cell at the end of each line (in detail fields). 
	///			 They are generated by a small program. they have text like: fldPlan01+fldPlan02+ ...
	///			 These C1 VB may work well, but I really don't love it like the first time I see C1. Sometime it raise a very crazy unknown bsht.
	/// --- count "Achieve", count "Not Achieve" on each row is now process by the C1
	/// 
	/// --- Some field on the report is calculate by the VBScript (store on the report layout XML file). 
	/// For example: calc some percent cell on each detail row.
	/// Remember that the C1Report VBScript count() function is not work well , so in the last to rows (grand sum by day, at the bottom of report): AchieveDay and Not Achieve/Day, we should calculate these values by C# (and I did that)
	/// 
	/// --- About the chart:
	/// We put 3 first rows of report (PlanTotal, Actual Total, Progress Total) to the Excel files to generate the DetailChart
	/// We put AchievePercent (right most, bottom most field) to the "ProportionAchievePlan" Excel cell
	/// put ProportionStandard (right most, near top field) to the "ProportionStandard" Excel cell
	/// Then we copy 2 chart from Excel file to images, bind to the fldChart and fldTotalChart on the C1Report
	/// 
	/// The last line I drop here: DON"T BELIEVE IN 3rd Vendor COmponent Provider. C1REport is blsht when you processing huge report.
	/// </summary>
	[Serializable]	
	public class VendorDeliveryAssessment : MarshalByRefObject, IDynamicReport		            
	{
		#region IDynamicReport Implementation
	
		private string mConnectionString;
		private ReportBuilder mReportBuilder = new ReportBuilder();		
		private C1PrintPreviewControl mReportViewer;
		private bool mUseReportViewerRenderEngine = false;	

		private string mstrReportDefinitionFolder = string.Empty;
		string mstrReportLayoutFile = string.Empty;

		private object mResult;

		/// <summary>
		/// ConnectionString, provide for the Dynamic Report
		/// ALlow Dynamic Report to access the DataBase of PCS
		/// </summary>
		public string PCSConnectionString
		{
			get { return mConnectionString; }
			set { mConnectionString = value; }
		}

		/// <summary>
		/// Report Builder Utility Object
		/// Dynamic Report can use this object to render, modify, layout the report
		/// </summary>
		public ReportBuilder PCSReportBuilder
		{
			get { return mReportBuilder; }
			set { mReportBuilder = value; }
		}

		/// <summary>
		/// ReportViewer Object, provide for the DynamicReport, 
		/// allow Dynamic Report to manipulate with the REportViewer, 
		/// modify the report after rendered if needed
		/// </summary>
		public C1PrintPreviewControl PCSReportViewer
		{
			get { return mReportViewer; }
			set { mReportViewer = value; }
		}

		/// <summary>
		/// Store other result if any. Ussually we store return DataTable here to display on the ReportViewer Form's Grid
		/// </summary>
		public object Result
		{
			get { return mResult; }
			set { mResult = value; }
		}

		/// <summary>
		/// Notify PCS whether the rendering report process is run by
		/// this IDynamicReport 
		/// or the ReportViewer Engine (in the ReportViewer form) 
		/// </summary> 		
		public bool UseReportViewerRenderEngine { get { return mUseReportViewerRenderEngine; } set { mUseReportViewerRenderEngine = value; } }

		
		/// <summary>
		/// Inform External Process where to find out the ReportLayout	 ( the PCS' ReportDefinition Folder Path )
		/// </summary>				
		public string ReportDefinitionFolder
		{
			get 
			{
				return mstrReportDefinitionFolder;
			}
			set
			{
				mstrReportDefinitionFolder = value;
			}
		}


	
		/// <summary>
		/// Inform External Process where to find out the ReportLayout	 ( the PCS' ReportDefinition Folder Path )
		/// </summary>				
		public string ReportLayoutFile
		{
			get 
			{
				return mstrReportLayoutFile;
			}
			set
			{
				mstrReportLayoutFile = value;
			}
		}

		public object Invoke(string pstrMethod, object[] pobjParameters)
		{			
			return this.GetType().InvokeMember(pstrMethod, BindingFlags.InvokeMethod, null, this, pobjParameters);
		}


		#endregion

		

		public VendorDeliveryAssessment()
		{
		}		

		
	
		#region GLOBAL CONSTANT
		
		const string THIS = "ExternalReportFile:VendorDeliveryAssessment";
		const string METHOD_NAME = THIS + ".ExecuteReport()";

		const string TABLE_NAME = "VendorDeliveryAssessment";	
		const string ZERO_STRING = "0";
		const string ASSESSMENT_OK = "O";
		const string ASSESSMENT_NG = "X";
		const string MONTH_DATE_FORMAT = "MMM";

		/// Report layout file constant
		const string REPORT_LAYOUT_FILE = "VendorDeliveryAssessment.xml";
		const string REPORT_NAME = "VendorDeliveryAssessment";
		short COPIES = 1;

		/// all parameter are Mandatory
		const string REPORTFLD_PARAMETER_CCN						= "fldParameterCCN";
		const string REPORTFLD_PARAMETER_MONTH					= "fldParameterMonth";
		const string REPORTFLD_PARAMETER_YEAR						= "fldParameterYear";			
		const string REPORTFLD_PARAMETER_VENDOR		= "fldParameterParty";
		const string REPORTFLD_PARAMETER_POREVISION1			= "fldParameterPORevision1";
		const string REPORTFLD_PARAMETER_POREVISION2			= "fldParameterPORevision2";		
		const string REPORTFLD_PROPORTIONSTANDARDPERCENT	= "fldProportionStandardPercent";

		/// Result Data Table Column names, contain:
		/// PRODUCTID, CATEGORY,PARTNO,MODEL,BEGIN
		/// DATE
		/// PLAN+QUANTITY, ACTUAL+QUANTITY
		const string PRODUCTID = "ProductID";
		const string CATEGORY = "Category";
		const string PARTNO = "Part No.";
		const string PARTNAME = "PartName";
		const string MODEL = "Model";
		const string BEGIN = "ProgressBeginQuantity";

		const string DATE = "Day";
		const string QUANTITY = "Quantity";	// suffix for PLAN,ACTUAL , RETURN column

		const string PREFIX_DAYINMONTH = "lblDayInMonth";
		const string PREFIX_DAYOFWEEK = "lblDayOfWeek";


		/// other constants			
		const string PLAN = "Plan";
		const string ADJ = "Adj";
		const string ACTUAL = "Actual";
		const string PROGRESSDAY = "ProgressDay";
		const string PROGRESS = "ProgressAccumulate";
		const string ASSESSMENT = "Assessment";
		const string RETURN = "Return";
		const string ROWCOUNTPASS = "RowCountPass";
		const string ROWCOUNTFAIL = "RowCountFail";
		const string ROWPERCENT = "RowPercent";

		const string FLD = "fld";		
		const string LBL = "lbl";
		const string HEADING = "DayHeading";


		const string PLANFAIL = "PlanFailD";
		const string PLANPASS = "PlanPassD";

		/// chart fields
		const string REPORTFLD_CHART	= "fldChart";
		const string REPORTFLD_TOTALCHART = "fldTotalChart";

		const string REPORTFLD_TOTALPASS = "fldPlanPassSumRow";
		const string REPORTFLD_TOTALFAIL = "fldPlanFailSumRow";		

		#endregion GLOBAL CONSTANT


		#region GLOBAL VAR
		

		DataSet dstPLPP = new DataSet();
		
		string PLAN_TABLE_NAME_1 = "PlanTable1";
		string PLAN_TABLE_NAME_2 = "PlanTable2";
		string ACTUAL_TABLE_NAME = "ActualTable";
		string ADJ_TABLE_NAME = "AdjTable";
		string RETURN_TO_VENDOR_TABLE_NAME = "ReturnTable";

		#endregion GLOBAL VAR

		/// <summary>
		/// Main function, generate the result data Table for the REPORT VIEWER
		/// Modify the REPORT VIEWER to display the report
		/// </summary>
		/// <param name="pstrCCNID"></param>
		/// <param name="pstrYear"></param>
		/// <param name="pstrMonth"></param>
		/// <param name="pstrPartyID"></param>
		/// <param name="pstrPurchaseOrderMasterID_1"></param>
		/// <param name="pstrPurchaseOrderMasterID_2"></param>
		/// <param name="pstrProportionStandard">You must fill 0.xx here ( a number less than 1 and greater or equal 0)</param>
		/// <returns></returns>
		public DataTable ExecuteReport(string pstrCCNID, string pstrYear, string pstrMonth, string pstrPartyID, string pstrPORevision_1, string pstrPORevision_2, string pstrProportionStandard)
		{
			#region My variables

			int nCCNID = int.Parse(pstrCCNID);
			int nMonth = int.Parse(pstrMonth);
			int nYear = int.Parse(pstrYear);			
			int nPartyID = int.Parse(pstrPartyID);			
			int nRevision_2 = int.Parse(pstrPORevision_2);			
			int nRevision_1 = GetPreviousPORevision(pstrCCNID, pstrYear,  pstrMonth, pstrPartyID, pstrPORevision_2);
			
			double dblProportionStandard = 0.95d;
			try
			{
				dblProportionStandard = Convert.ToDouble(pstrProportionStandard);
			}
			catch{}	// not mandatory, so we will the default value 0.95 for other processing
			
			string strCCN = string.Empty;
			string strMonth = pstrMonth;
			string strYear = pstrYear;
			string strParty = string.Empty;
			string strRevision1 = nRevision_1 < 0 ? pstrPORevision_1 : nRevision_1.ToString();
			string strRevision2 = pstrPORevision_2;
						
			float fActualPageSize = 9000.0f;			

			/// contain array of string: Name of the column (with days have data in the dtbSourceData)
			/// FOr Example:
			/// dtbSourceData contain: 01-Oct: has Plan Quantity
			/// 02-Oct has Actual Quantity
			/// So arrHasValueDateHeading contain: Plan01, Actual02
			ArrayList arrHasValueDateHeading = new ArrayList();	

			/// Build to keep value of pair: 01 --> 01-Mon, ... depend on the real data of dtbResule
			NameValueCollection arrDayNumberMapToDayWithDayOfWeek = new NameValueCollection();

			/// Keep count of PlanPass and PlanFail for all days (columns).
			Hashtable arrColumnPass = new Hashtable();
			Hashtable arrColumnFail = new Hashtable();

			/// Keep count of PlanPass and PlanFail for all ITEMS  (rows).
			Hashtable arrRowPass = new Hashtable();
			Hashtable arrRowFail = new Hashtable();


			// get data and cache all in the dstPLPP
			dstPLPP.DataSetName = pstrCCNID + pstrYear + pstrMonth + pstrPartyID + pstrPORevision_1 + pstrPORevision_2 + pstrProportionStandard;			
			GetDataAndCache(pstrCCNID, pstrYear, pstrMonth, pstrPartyID, 
				strRevision1, strRevision2, pstrProportionStandard);		
			
			System.Data.DataTable dtbPlanTable;
			dtbPlanTable  = dstPLPP.Tables[PLAN_TABLE_NAME_2];		
			
			System.Data.DataTable dtbActualTable;
			dtbActualTable = dstPLPP.Tables[ACTUAL_TABLE_NAME];

			System.Data.DataTable dtbPlanTableWO1;
			System.Data.DataTable dtbAdjTable;
			dtbPlanTableWO1  = dstPLPP.Tables[PLAN_TABLE_NAME_1];
			dtbAdjTable = BuildAdjTable(dtbPlanTableWO1 , dtbPlanTable);
			dtbAdjTable.TableName = ADJ_TABLE_NAME;
			dstPLPP.Tables.Add(dtbAdjTable);
			
			System.Data.DataTable dtbReturnTable;
			dtbReturnTable = dstPLPP.Tables[RETURN_TO_VENDOR_TABLE_NAME];

			#endregion  My Variables

			#region	GETTING THE PARAMETER
			PCSComUtils.Common.BO.UtilsBO boUtil = new PCSComUtils.Common.BO.UtilsBO();
			PCSComUtils.Framework.ReportFrame.BO.C1PrintPreviewDialogBO objBO = new PCSComUtils.Framework.ReportFrame.BO.C1PrintPreviewDialogBO();
			strCCN = boUtil.GetCCNCodeFromID(nCCNID);	
			strParty = objBO.GetVendorCodeFromID(nPartyID) + ": " + objBO.GetVendorNameFromID(nPartyID);

		
			
			#endregion	
			
			/// transform TABLE column names
			/// transform TABLE will contain :
			/// PRODUCTID, CATEGORY,PARTNO,MODEL,
			/// BEGIN
			/// PLAN+i.ToString("00")
			/// ADJ +i.ToString("00")
			/// ACTUAL+i.ToString("00")
			/// RETURN+i.ToString("00")
			#region TRANSFORM ORIGINAL TABLE FOR REPORT
	
			#region GETTING THE DATE HEADING
			/// arrPlanDate and arrActualDate contain DateTime object from actual dtbSourceData
			ArrayList arrPlanDate = GetColumnValuesFromTable(dtbPlanTable,PLAN+DATE);			
			ArrayList arrActualDate = GetColumnValuesFromTable(dtbActualTable,ACTUAL+DATE);
			ArrayList arrReturnDate = GetColumnValuesFromTable(dtbReturnTable,RETURN+DATE);			
			ArrayList arrAdjDate = GetColumnValuesFromTable(dtbAdjTable,ADJ+DATE);

			ArrayList arrItems = GetCategory_PartNo_Model_ProductID_FromTable(dtbPlanTable,CATEGORY,PARTNO,MODEL,PRODUCTID);

			/// PUSH: has-value (in the dtbSourceData) to the arrHasValueDateHeading
			/// 
			/// HACKED: Thachnn: 20/12/2005
			/// don't remove this dummy code of casting object in arrPlanDate to int
			/// because sometime, data in the database is not correct, return dbnull to the arrPlanDate. If we use normal foreach(int nDay in arrPlanDate)
			/// exception of Invalid cast will be throw
			/// In this case: ActualDate can be omit and = DBNull because an Item can be Plan, but it didn't produce in any day in this month
			foreach(object obj  in arrPlanDate)
			{
				try
				{
					int nDay = (int)obj;
					DateTime dtm = new DateTime(nYear,nMonth,nDay);
					string strColumnName = PLAN + dtm.Day.ToString("00");
					arrDayNumberMapToDayWithDayOfWeek.Add(dtm.Day.ToString("00"), dtm.Day.ToString("00")+"-"+dtm.DayOfWeek.ToString().Substring(0,3)  );
					arrHasValueDateHeading.Add(strColumnName);
				}
				catch{}
			}
			
			foreach(object obj in arrActualDate)
			{
				try
				{
					int nDay = (int)obj;
					DateTime dtm = new DateTime(nYear,nMonth,nDay);
					string strColumnName = ACTUAL + dtm.Day.ToString("00");
					arrDayNumberMapToDayWithDayOfWeek.Add(dtm.Day.ToString("00"), dtm.Day.ToString("00")+"-"+dtm.DayOfWeek.ToString().Substring(0,3)  );
					arrHasValueDateHeading.Add(strColumnName);
				}
				catch{}
			}
			foreach(object obj in arrReturnDate)
			{
				try
				{
					int nDay = (int)obj;
					DateTime dtm = new DateTime(nYear,nMonth,nDay);
					string strColumnName = RETURN + dtm.Day.ToString("00");
					arrDayNumberMapToDayWithDayOfWeek.Add(dtm.Day.ToString("00"), dtm.Day.ToString("00")+"-"+dtm.DayOfWeek.ToString().Substring(0,3)  );
					arrHasValueDateHeading.Add(strColumnName);
				}
				catch{}
			}

			foreach(object obj in arrAdjDate)
			{
				try
				{
					int nDay = (int)obj;
					DateTime dtm = new DateTime(nYear,nMonth,nDay);
					string strColumnName = ADJ + dtm.Day.ToString("00");
					arrDayNumberMapToDayWithDayOfWeek.Add(dtm.Day.ToString("00"), dtm.Day.ToString("00")+"-"+dtm.DayOfWeek.ToString().Substring(0,3)  );
					arrHasValueDateHeading.Add(strColumnName);
				}
				catch{}
			}
			/// ENDHACKED: Thachnn: 20/12/2005
			/// after this snip of code. arrHasValueDateHeading will contain Actual01, Actual02 or Plan03 Plan04, or Adj03 Adj04, Return02, Return03  ... 
			/// depend on the DataTable
			/// Which day has value (Plan , Adj, or Actual,Return), the columnName will exist in the arrHasValueDateHeading
			/// and then, the Transform DataTable dtbTransform will has some columns named like string in arrHasValueDateHeading			

			#endregion		
            
			#region BUILD TRANSFORM TABLE SCHEMA		
			DataTable dtbTransform = BuildTransformTable(arrHasValueDateHeading);
			#endregion
			
			#region FILL ABSOLUTE DATA FROM Plan && Actual && Adjust && Return to the TRANSFORM DATATABLE
			
			/// GUIDE: with each Items
			foreach(string strItem in arrItems)
			{
				// Create DUMMYROW FIRST
				DataRow dtrNew = dtbTransform.NewRow();
						
				#region	- fill ITEM info and plan quantity to the new dummy row				
				
				/// if strItem.Split('#')[0] ==  string.empty, its mean Category value is null
				/// so we put IsNull in the filter string (to select from dtbResult);
				string strFilter = string.Empty;
				if(strItem.Split('#')[0] == string.Empty)
				{
					strFilter = 
						string.Format("[{0}] is null AND [{1}]='{2}' AND [{3}]='{4}'",
						CATEGORY,							
						PARTNO,
						strItem.Split('#')[1],
						MODEL,
						strItem.Split('#')[2]
						);
				}
				else
				{
					strFilter = 
						string.Format("[{0}]='{1}' AND [{2}]='{3}' AND [{4}]='{5}'",
						CATEGORY,
						strItem.Split('#')[0],
						PARTNO,
						strItem.Split('#')[1],
						MODEL,
						strItem.Split('#')[2]
						);
				}

				string strSort = string.Format("[{0}] ASC,[{1}] ASC,[{2}] ASC ",CATEGORY,PARTNO,MODEL);
                /// GUIDE: get all rows of this Item from the dtbSourceData
				DataRow[] dtrows = dtbPlanTable.Select(strFilter,strSort);

				/// GUIDE: for each rows in of this Item OF DTBSourceData - fill plan quantity and some meta info about ITEM
				foreach(DataRow dtr in dtrows)
				{
					// fill data to the dummy row
					dtrNew[PRODUCTID] = dtrows[0][PRODUCTID];
					dtrNew[CATEGORY] = dtrows[0][CATEGORY];
					dtrNew[PARTNO] = dtrows[0][PARTNO];
					dtrNew[PARTNAME] = dtrows[0][PARTNAME];
					dtrNew[MODEL] = dtrows[0][MODEL];
					dtrNew[BEGIN] = dtrows[0][BEGIN];

					/// Fill Plan Quantity to destination column of Transform table, in this new rows
					//string strDateColumnToFill = PLAN + ((DateTime)dtr[PLAN+DATE]).Day.ToString("00");
					string strDateColumnToFill = PLAN + Convert.ToInt32( dtr[PLAN+DATE]).ToString("00");
					dtrNew[strDateColumnToFill] = dtr[PLAN+QUANTITY];				
				}

				#endregion - fill ITEM info and plan quantity to the new dummy row
				
				
				#region - fill actual quantity to the new dummy row

				/// if strItem.Split('#')[0] ==  string.empty, its mean Category value is null
				/// so we put IsNull in the filter string (to select from dtbResult);
				string strFilterActualCompletion = string.Empty;
				strFilterActualCompletion = 
					string.Format("[{0}]='{1}' ",
					PRODUCTID,
					strItem.Split('#')[3]
					);		
				
				/// GUIDE: get all rows of this Item from the dtbSourceData
				DataRow[] dtrowsActualCompletion = dtbActualTable.Select(strFilterActualCompletion);

				/// GUIDE: for each rows  of this Item in Actual Completion DataTable- fill actual quantity to the dummy ROW
				foreach(DataRow dtr in dtrowsActualCompletion)
				{
					/// Fill Actual Quantity to destination column of Transform table, in this new rows
					//strDateColumnToFill = ACTUAL + ((DateTime)dtr[ACTUAL+DATE]).Day.ToString("00");
					string strDateColumnToFill = ACTUAL + Convert.ToInt32( dtr[ACTUAL+DATE]).ToString("00");
					dtrNew[strDateColumnToFill] = dtr[ACTUAL+QUANTITY];
				}
				#endregion - fill actual  quantity to the new dummy row

			
				#region - fill adjust quantity to the new dummy row
				
				/// so we put IsNull in the filter string (to select from dtbResult);
				string strFilterAdjust = string.Empty;
				strFilterAdjust = 
					string.Format("[{0}]='{1}' ",
					PRODUCTID,
					strItem.Split('#')[3]
					);		
				
				/// GUIDE: get all rows of this Item from the dtbSourceData
				DataRow[] dtrowsAdjust = dtbAdjTable.Select(strFilterAdjust);

				/// GUIDE: for each rows  of this Item in Adjust DataTable- fill actual quantity to the dummy ROW
				foreach(DataRow dtr in dtrowsAdjust)
				{
					/// Fill Actual Quantity to destination column of Transform table, in this new rows
					//strDateColumnToFill = ADJ + ((DateTime)dtr[ADJ +DATE]).Day.ToString("00");
					string strDateColumnToFill = ADJ + Convert.ToInt32( dtr[ADJ+DATE]).ToString("00");
					dtrNew[strDateColumnToFill] = dtr[ADJ+QUANTITY];
				}
				#endregion - fill adjust quantity to the new dummy row
	

				#region - fill return quantity to the new dummy row

				/// if strItem.Split('#')[0] ==  string.empty, its mean Category value is null
				/// so we put IsNull in the filter string (to select from dtbResult);
				string strFilterReturn = string.Empty;
				strFilterReturn = 
					string.Format("[{0}]='{1}' ",
					PRODUCTID,
					strItem.Split('#')[3]
					);		
				
				/// GUIDE: get all rows of this Item from the dtbSourceData
				DataRow[] dtrowsReturn = dtbReturnTable.Select(strFilterReturn);

				/// GUIDE: for each rows  of this Item in Return DataTable- fill return quantity to the dummy ROW
				foreach(DataRow dtr in dtrowsReturn)
				{
					/// Fill Return Quantity to destination column of Transform table, in this new rows
					//strDateColumnToFill = RETURN + ((DateTime)dtr[RETURN+DATE]).Day.ToString("00");
					string strDateColumnToFill = RETURN + Convert.ToInt32( dtr[RETURN+DATE]).ToString("00");
					dtrNew[strDateColumnToFill] = dtr[RETURN+QUANTITY];
				}
				#endregion - fill Return quantity to the new dummy row


				// add to the transform data table
				dtbTransform.Rows.Add(dtrNew);				
			}	    
			#endregion FILL DATA FROM Plan DTB && ActualCompletion DTB && Adjust DTB to the TRANSFORM DATATABLE

			#endregion  TRANSFORM ORIGINAL TABLE FOR REPORT

			#region calculate the Sum of Plan, sum of Actual, sum of Progress (on top of the report) to generate a chart in EXCEL			
			double[,] arrSumPlan = new double[1,31];
			double[,] arrSumActual = new double[1,31];
			double[,] arrSumProgress = new double[1,31]; 
			
			for(int i = 1 ; i <= DateTime.DaysInMonth(nYear,nMonth)  ; i++)
			{
				string strCounter = i.ToString("00");

				/// sum on the top of the report, calculate to put in the excel file to generate a chart.
				//string str = "Sum(Plan"+i.ToString("00")+")";
				try
				{
					arrSumPlan[0,i-1] = double.Parse(dtbTransform.Compute("Sum(Plan"+i.ToString("00")+")" , string.Empty ).ToString());
				}
				catch{}
				
				try
				{
					arrSumActual[0,i-1] = double.Parse(dtbTransform.Compute("Sum(Actual"+i.ToString("00")+")" , string.Empty).ToString());
				}
				catch{}

				/// progress SUm will be caculate in the next section , after render the report, we will get the real value of upper sum field on report
				/// because the progress value is caculate on render time, depend on the real actual data on the rendered report				
			}		// end foreach Day(i)
			#endregion calculate the Sum of Plan, sum of Actual, sum of Progress (on top of the report) to generate a chart in EXCEL

			#region calculate the ProgressDay column

			for(int i = 1 ; i <= DateTime.DaysInMonth(nYear,nMonth); i++)
			{
				string strCounter = i.ToString(ReportBuilder.FORMAT_DAY_2CHAR);				
				foreach(DataRow rowItem in dtbTransform.Rows)
				{
					decimal decPlan = ReportBuilder.ToDecimal(rowItem[PLAN+strCounter]);
					decimal decActual = ReportBuilder.ToDecimal(rowItem[ACTUAL+strCounter]);
					decimal decReturn = ReportBuilder.ToDecimal(rowItem[RETURN+strCounter]);					

					rowItem[PROGRESSDAY+strCounter] = decActual - decPlan - decReturn;
				}			
			}	

			#endregion calculate the ProgressDay column

			#region - calculate , fill Progress quantity to the new dummy row

			for(int i = 1 ; i <= 31 /*DateTime.DaysInMonth(nYear,nMonth)*/  ; i++)
			{
				string strCounter = i.ToString(ReportBuilder.FORMAT_DAY_2CHAR);
                
				foreach(string strItem in arrItems)
				{                    			
					/// strItem.Split('#')[3] ==  PRODUCTID					
					string strFilterProgress = 
						string.Format("[{0}]='{1}' ",
						PRODUCTID,
						strItem.Split('#')[3]
						);
				
					/// GUIDE: get rows ( in fact, it is only one) of this Item from the dtbTransform
					DataRow[] dtrowsItemAllInfo = dtbTransform.Select(strFilterProgress);
			
					decimal decCurrentACTUAL = ReportBuilder.ToDecimal( dtrowsItemAllInfo[0][ACTUAL+ i.ToString("00")] );
					decimal decCurrentPLAN = ReportBuilder.ToDecimal( dtrowsItemAllInfo[0][PLAN+ i.ToString("00")]) ;
					decimal decCurrentRETURN = ReportBuilder.ToDecimal( dtrowsItemAllInfo[0][RETURN+ i.ToString("00")]) ;
					
					decimal decPreviousPROGRESS = decimal.Zero;
					if(i == 1)
						decPreviousPROGRESS = ReportBuilder.ToDecimal( dtrowsItemAllInfo[0][BEGIN] );
					else					
						decPreviousPROGRESS = ReportBuilder.ToDecimal( dtrowsItemAllInfo[0][PROGRESS + (i-1).ToString("00")]  ) /*Previous*/ ;					

					dtrowsItemAllInfo[0][PROGRESS + i.ToString("00")] = 
						decPreviousPROGRESS
						+decCurrentACTUAL
						-decCurrentPLAN
						-decCurrentRETURN;
					
				}	// end each Items (of current day  = i)
			}		// end foreach Day(i)
			
			#endregion - calculate , fill Progress quantity to the new dummy row			
			
			int intTotalCountPass = 0;			int intTotalCountFail = 0;						
			#region : ASSESS the PROGRESS, fill ASSESSMENT and CALCULATE the count of FAIL and PASS
			for(int i = 1 ; i <= DateTime.DaysInMonth(nYear,nMonth); i++)
			{
				int intColumnPass = 0;			int intColumnFail = 0;
				string strCounter = i.ToString(ReportBuilder.FORMAT_DAY_2CHAR);		
				
				foreach(DataRow rowItem in dtbTransform.Rows)
				{					
					decimal decPlan = ReportBuilder.ToDecimal(rowItem[PLAN+strCounter]);
					decimal decActual = ReportBuilder.ToDecimal(rowItem[ACTUAL+strCounter]);
					decimal decReturn = ReportBuilder.ToDecimal(rowItem[RETURN+strCounter]);
					decimal decProgressDay = decActual - decPlan - decReturn;
					decimal decProgress = ReportBuilder.ToDecimal(rowItem[PROGRESS+strCounter]);					

					if (decPlan == decimal.Zero  && decActual == decimal.Zero )
					{ /* Ignore, don't assess the progress */ }
					else
					{
						if (decProgressDay == decimal.Zero)
						{
							rowItem[ASSESSMENT+strCounter] = ASSESSMENT_OK;
							intTotalCountPass ++;	// total
                            rowItem[ROWCOUNTPASS] = ReportBuilder.ToInt32(rowItem[ROWCOUNTPASS]) + 1;
							intColumnPass++;							
						}
						else if ((decProgressDay > 0) && (decProgress <= 0))
						{
							rowItem[ASSESSMENT+strCounter] = ASSESSMENT_OK;
							intTotalCountPass ++;
							rowItem[ROWCOUNTPASS] = ReportBuilder.ToInt32(rowItem[ROWCOUNTPASS]) + 1;
							intColumnPass++;
						}
						else
						{
							rowItem[ASSESSMENT+strCounter] = ASSESSMENT_NG;
							intTotalCountFail ++;
							rowItem[ROWCOUNTFAIL] = ReportBuilder.ToInt32(rowItem[ROWCOUNTFAIL]) + 1;
							intColumnFail++;
						}
					}										
				}	// end each rowItem in Transform table				
				arrColumnPass.Add(FLD + PLANPASS + strCounter, intColumnPass);
				arrColumnFail.Add(FLD + PLANFAIL  + strCounter, intColumnFail );
			}	// end foreach i			

			#endregion calculate the count of Plan FAIL and Plan PASS			
			
			#region CALCULATE the Percent for each Row
			foreach(DataRow rowItem in dtbTransform.Rows)
			{
				int nSum = ReportBuilder.ToInt32(rowItem[ROWCOUNTPASS]) + ReportBuilder.ToInt32(rowItem[ROWCOUNTFAIL]);
				if( nSum != 0 )
				{
					double dblPercent = (double)ReportBuilder.ToInt32(rowItem[ROWCOUNTPASS]) / nSum;
					rowItem[ROWPERCENT] = (dblPercent*100).ToString("#0.00") + "%";
				}					
			}
			#endregion CALCULATE the Percent for each Row

			#region  SHORT CIRCURT this function			
//			if((intTotalCountPass + intTotalCountFail) == 0 )
//			{
//				mResult = "-";
//			}
//			else
//			{
//				decimal decTemp  = ( (decimal)intTotalCountPass * 100) / (intTotalCountPass + intTotalCountFail) ;
//				mResult = decTemp.ToString("#,##0.00");
//			}
//			return new DataTable();
			#endregion SHORT CIRCURT this function


			#region RENDER REPORT
			
			ReportBuilder objRB = new ReportBuilder();
			objRB.ReportName = REPORT_NAME;
			objRB.SourceDataTable = dtbTransform;
			
			#region INIT REPORT BUIDER OBJECT
			try
			{
				objRB.ReportDefinitionFolder = mstrReportDefinitionFolder;
				objRB.ReportLayoutFile = mstrReportLayoutFile;
				if(objRB.AnalyseLayoutFile() == false)
				{
					//					PCSMessageBox.Show(ErrorCode.MESSAGE_REPORT_TEMPLATE_FILE_NOT_FOUND, MessageBoxIcon.Error);
					return new DataTable();
				}
				//objRB.UseLayoutFile = objRB.AnalyseLayoutFile();	// use layout file if any , auto drawing if not found layout file
				objRB.UseLayoutFile = true;	// always use layout file
			}
			catch
			{
				objRB.UseLayoutFile = false;
				//				PCSMessageBox.Show(ErrorCode.MESSAGE_REPORT_TEMPLATE_FILE_NOT_FOUND,MessageBoxIcon.Error);
			}

			C1.C1Report.Layout objLayout = objRB.Report.Layout;
			fActualPageSize = objLayout.PageSize.Width - (float)objLayout.MarginLeft - (float)objLayout.MarginRight;
			#endregion				
		
			objRB.MakeDataTableForRender();
				
			// and show it in preview dialog				
			PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog printPreview = new PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog();				
			printPreview.FormTitle = REPORT_NAME;
			objRB.ReportViewer = printPreview.ReportViewer;			
			objRB.RenderReport();			


			if(dtbPlanTable.Rows.Count > 0)
			{	
				for(int i = 0 ; i <= DateTime.DaysInMonth(nYear,nMonth) ;  )
				{				
					try
					{
						arrSumProgress[0,i] = (double)objRB.GetFieldByName(FLD + "Sum" + PROGRESS + (++i).ToString("00")).Value;
					}
					catch{}
				}


				#region BUILD CHART, save to image in clipboard, and then put in the report field fldChart		
				
			

				Field fldChart = objRB.GetFieldByName(REPORTFLD_CHART);
				Field fldTotalChart = objRB.GetFieldByName(REPORTFLD_TOTALCHART);
			
				#region	INIT CHART WITH EXCEL

//				string EXCEL_REPORT_FOLDER = "ExcelReport";			
				string EXCEL_FILE = "VendorDeliveryAssessment.xls";
			
//				if( ! Directory.Exists(APP_PATH + Path.DirectorySeparatorChar + 	EXCEL_REPORT_FOLDER) )
//				{
//					Directory.CreateDirectory(APP_PATH + Path.DirectorySeparatorChar + 	EXCEL_REPORT_FOLDER);
//				}

//				string strTemplateFilePath = APP_PATH + Path.DirectorySeparatorChar + 
//					REPORT_DEFINITION_FOLDER + Path.DirectorySeparatorChar + 
//					EXCEL_FILE;
				string strTemplateFilePath = mstrReportDefinitionFolder + Path.DirectorySeparatorChar + EXCEL_FILE;

//				string strDestinationFilePath = APP_PATH + Path.DirectorySeparatorChar + 
//					EXCEL_REPORT_FOLDER + Path.DirectorySeparatorChar + 
//					Path.GetFileNameWithoutExtension(EXCEL_FILE) + FormControlComponents.NowToUTCString() + ".XLS";

				string strDestinationFilePath = mstrReportDefinitionFolder + Path.DirectorySeparatorChar + 
					Path.GetFileNameWithoutExtension(EXCEL_FILE) + FormControlComponents.NowToUTCString() + ".XLS";

				/// Copy layout excel report file to ExcelReport folder with a UTC datetime name
				File.Copy(strTemplateFilePath,	strDestinationFilePath,	true);


				PCSUtils.Utils.ExcelReportBuilder objXLS = new ExcelReportBuilder(strDestinationFilePath);
			
			
				#endregion

				try
				{            		        
					#region BUILD THE REPORT ON EXCEL FILE
                
					objXLS.GetRange("A1", "AE1").Value2 = arrSumPlan;
					objXLS.GetRange("A2", "AE2").Value2 = arrSumActual;
					objXLS.GetRange("A3", "AE3").Value2 = arrSumProgress;

					DateTime dtmForExcel = new DateTime(nYear,nMonth,1);
					string[] arrExcelColumnHeading = new string[DateTime.DaysInMonth(nYear,nMonth)];
					for(int i = 1; i <= DateTime.DaysInMonth(nYear,nMonth) ; i++ )
					{						
						arrExcelColumnHeading[i-1] = i.ToString(ReportBuilder.FORMAT_DAY_2CHAR) +ReportBuilder.SEPERATOR_DATETIME + dtmForExcel.ToString(ReportBuilder.FORMAT_MONTH_3CHAR)  /* + ReportBuilder.SEPERATOR_DATETIME */ +"\n" + dtmForExcel.ToString("ddd");
						dtmForExcel = dtmForExcel.AddDays(1);
					}
					objXLS.GetRange("A4","AE4").Value2 = arrExcelColumnHeading;

					// DONE: Replace Proportion Standard with input Parameter
					objXLS.GetRange("ProportionStandard",Type.Missing).Value2		= dblProportionStandard;
					objXLS.GetRange("ProportionAchievePlan",Type.Missing ).Value2	= objRB.GetFieldByName("fldAchievePercent");

					Excel.ChartObject chart = objXLS.GetChart("DetailChart");
					/// PLAN
					((Excel.Series)chart.Chart.SeriesCollection(1)).Values =  objXLS.GetRange( objXLS.GetCell(1,1),objXLS.GetCell(1, DateTime.DaysInMonth(nYear,nMonth)   ) );
					/// ACTUAL
					((Excel.Series)chart.Chart.SeriesCollection(2)).Values =  objXLS.GetRange( objXLS.GetCell(2,1),objXLS.GetCell(2, DateTime.DaysInMonth(nYear,nMonth)   ) );
					/// PROGRESS
					((Excel.Series)chart.Chart.SeriesCollection(3)).Values =  objXLS.GetRange( objXLS.GetCell(3,1),objXLS.GetCell(3, DateTime.DaysInMonth(nYear,nMonth)   ) );


					chart.Chart.CopyPicture(Excel.XlPictureAppearance.xlScreen,Excel.XlCopyPictureFormat.xlBitmap,
						Excel.XlPictureAppearance.xlScreen);
					Image image =  (Image)Clipboard.GetDataObject().GetData(typeof(Bitmap));
					/// DEBUG: to view the chart export to image
					/// image.Save("c:\\"+EXCEL_FILE+".chartExcel.Emf",System.Drawing.Imaging.ImageFormat.Emf);
					fldChart.Visible = true;
					fldChart.Text = "";
					fldChart.Picture = image;

					chart = objXLS.GetChart("TotalChart");
					chart.Chart.CopyPicture(Excel.XlPictureAppearance.xlScreen,Excel.XlCopyPictureFormat.xlBitmap,
						Excel.XlPictureAppearance.xlScreen);
					image =  (Image)Clipboard.GetDataObject().GetData(typeof(Bitmap));
					/// DEBUG: to view the chart export to image
					/// image.Save("c:\\"+EXCEL_FILE+".chartExcel.Emf",System.Drawing.Imaging.ImageFormat.Emf);
					fldTotalChart.Visible = true;
					fldTotalChart.Text = "";
					fldTotalChart.Picture = image;

					#endregion
			
				}
				catch(Exception ex)
				{		
					/// Test: remove if needed
					/// MessageBox.Show("Can not inter operate with Excel: " + ex.Message,"Production Control System",MessageBoxButtons.OK,MessageBoxIcon.Error);
				}
				finally
				{			
					#region SAVE, CLOSE EXCEL FILE CONTAIN REPORT
					objXLS.CloseWorkbook();		
					objXLS.Dispose();
					objXLS = null;
					#endregion
				}				

				#endregion BUILD CHART		

			}

			
			#region MODIFY THE REPORT LAYOUT - do not modify data value on report from now on

			#region PUSH PARAMETER VALUE
			objRB.DrawPredefinedField(REPORTFLD_PARAMETER_CCN,strCCN);			
			objRB.DrawPredefinedField(REPORTFLD_PARAMETER_MONTH, pstrMonth);
			objRB.DrawPredefinedField(REPORTFLD_PARAMETER_YEAR, pstrYear);			
			objRB.DrawPredefinedField(REPORTFLD_PARAMETER_VENDOR, strParty);			
			objRB.DrawPredefinedField(REPORTFLD_PARAMETER_POREVISION1, strRevision1);
			objRB.DrawPredefinedField(REPORTFLD_PARAMETER_POREVISION2, strRevision2);			
			string strProportionStandard = dblProportionStandard.ToString() ;
			objRB.DrawPredefinedField(REPORTFLD_PROPORTIONSTANDARDPERCENT, strProportionStandard );
			#endregion		
			

			#region RENAME THE COLUMN HEADING TEXT
			ArrayList arrColumnHeadings = new ArrayList();				
			for(int i = 0; i <= 31; i++) /// clear the heading text
			{
				objRB.DrawPredefinedField(PREFIX_DAYINMONTH+i.ToString(ReportBuilder.FORMAT_DAY_2CHAR),"");
			}                
			objRB.DrawPredefinedList_DaysOfWeek(nYear, nMonth,
				PREFIX_DAYINMONTH,
				PREFIX_DAYOFWEEK,
				1 ,DateTime.DaysInMonth(nYear , nMonth ) );

			#endregion
			
			/// PUSH THE COUNT OF COLUMN COUNT FAIL AND COLUMN COUNT PASS
			foreach(DictionaryEntry objEntry in arrColumnPass)
			{				
				objRB.DrawPredefinedField(objEntry.Key.ToString()  ,objEntry.Value.ToString());				
			}
			foreach(DictionaryEntry objEntry in arrColumnFail)
			{				
				objRB.DrawPredefinedField(objEntry.Key.ToString()  ,objEntry.Value.ToString() );				
			}
			objRB.DrawPredefinedField(REPORTFLD_TOTALPASS , intTotalCountPass.ToString() );
			objRB.DrawPredefinedField(REPORTFLD_TOTALFAIL  , intTotalCountFail.ToString()   );

			#region HIDE the column of not-existed day in current month
			// 1. IN1T :: what to clear
			string[] arrFieldToClear = {
										   FLD + "Sum" + PLAN,
										   FLD + "Sum" + ACTUAL,	// Sum Return is not existed here
										   FLD + "Sum" + "ProgressAccumulate",			
										   PREFIX_DAYINMONTH,	/*// also hide the Day Heading */
										PREFIX_DAYOFWEEK,
				FLD + PLAN,
										   FLD + ADJ,
				FLD + ACTUAL,
										   FLD + RETURN,
										   FLD + PROGRESSDAY,
                FLD + PROGRESS,
				FLD + ASSESSMENT,
			
				FLD + PLANFAIL,
				FLD + PLANPASS,
				FLD + "PlanTotalPercentD"				
									   };		// contain name of field need to clear if day column is not exist
            
			objRB.HideColumnNotExistInMonth(nYear,nMonth, arrFieldToClear);

			#endregion HIDE the column of not-existed day in current month

			StringCollection arrFieldNames = new StringCollection();
			arrFieldNames.AddRange(arrFieldToClear);			

			string LEFT_ANCHOR_FLD = PREFIX_DAYINMONTH + "01";
			string LEFT_MARGIN_FLD = "lblLeftMarginToSpread";
			string RIGHT_MARGIN_FLD = "lblRightMarginToSpread";

			double dblWidthToSpead = objRB.ActualPageWidth - 
				(objRB.GetFieldByName(LEFT_MARGIN_FLD).Width + 	objRB.GetFieldByName(RIGHT_MARGIN_FLD).Width	) ;
			objRB.SpreadColumnsWithinWidth(arrFieldNames, 1,  DateTime.DaysInMonth(nYear,nMonth),
				objRB.GetFieldByName(LEFT_ANCHOR_FLD).Left, dblWidthToSpead  );

			objRB.MarkRedToNegativeNumberField();

			#endregion MODIFY THE REPORT LAYOUT - do not modify data value on report from now on
			
			objRB.RefreshReport();


			/// force the copies number
			printPreview.FormTitle = objRB.GetFieldByName("fldTitle").Text;			
			printPreview.Show();
			#endregion
			
			UseReportViewerRenderEngine = false;
			
			return dtbTransform;
		}
		

		/// <summary>
		/// Thachnn : 15/Oct/2005
		/// Browse the DataTable, get all value of column with provided named.
		/// </summary>
		/// <param name="pdtb">DataTable to collect values</param>
		/// <param name="pstrColumnName">COlumn Name in pdtb DataTable to collect values from</param>
		/// <returns>ArrayList of object, collect from pdtb's column named pstrColumnName. Empty ArrayList if error or not found any row in pdtb.</returns>
		private static ArrayList GetColumnValuesFromTable(DataTable pdtb, string pstrColumnName)
		{
			ArrayList arrRet = new ArrayList();
			try
			{
				foreach (DataRow drow in pdtb.Rows)
				{
					object objGet = drow[pstrColumnName];
					if( !arrRet.Contains(objGet)  )
					{
						arrRet.Add(objGet);
					}
				}
			}
			catch
			{
				arrRet.Clear();
			}
			return arrRet;
		}


		/// <summary>
		/// build a new datatable with column = productid, category,partno,model,begin,
		/// and somecolumn with names in arrHasValueDateHeading
		/// Index column is : Plan, Adj, Actual, Return, ProgressDay, Progress Accumulate, Assessment
		/// </summary>
		/// <remarks>		
		/// </remarks>
		/// <returns>DataTable</returns>
		private DataTable BuildTransformTable(ArrayList parrHasValueDateHeading)
		{
			DataTable dtbRet = new DataTable(REPORT_NAME);
			dtbRet.Columns.Add(PRODUCTID,typeof(System.Int32) );
			dtbRet.Columns.Add(CATEGORY,typeof(System.String) );
			dtbRet.Columns.Add(PARTNO,typeof(System.String));
			dtbRet.Columns.Add(PARTNAME,typeof(System.String));
			dtbRet.Columns.Add(MODEL,typeof(System.String));
			dtbRet.Columns.Add(BEGIN,typeof(System.Double));			
			dtbRet.Columns.Add(ROWCOUNTPASS,typeof(System.Int32));
			dtbRet.Columns.Add(ROWCOUNTFAIL,typeof(System.Int32));
			dtbRet.Columns.Add(ROWPERCENT,typeof(System.String));

			/// fill the column (Double type) in which the date exist in the dtbSourceData (has value contain in the parrDueDateHeading)
			/// then fill the column with String type (so that it will display correctly in the report, not #,##0.00, because it has null value)
					
			foreach(string strColumnName in parrHasValueDateHeading)
			{					
				try
				{
					dtbRet.Columns.Add(strColumnName,typeof(System.Double));
				}
				catch{}
			}
			// FILL the null column				
			for(int i = 1; i <=31; i++)												  
			{
				if(parrHasValueDateHeading.Contains(PLAN + i.ToString("00")) == false )
				{		
					try
					{
						dtbRet.Columns.Add(PLAN + i.ToString("00"),typeof(System.String));
						// TEST:						dtbRet.Columns[PLAN + i.ToString("00")].DefaultValue = 0d;
					}
					catch{}
				}
				if(parrHasValueDateHeading.Contains(ADJ + i.ToString("00")) == false )
				{		
					try
					{
						dtbRet.Columns.Add(ADJ + i.ToString("00"),typeof(System.String));
					}
					catch{}
				}
				if(parrHasValueDateHeading.Contains(ACTUAL + i.ToString("00")) == false )
				{		
					try
					{
						dtbRet.Columns.Add(ACTUAL + i.ToString("00"),typeof(System.String));
					}
					catch{}
				}

				if(parrHasValueDateHeading.Contains(RETURN + i.ToString("00")) == false )
				{		
					try
					{
						dtbRet.Columns.Add(RETURN + i.ToString("00"),typeof(System.String));
					}
					catch{}
				}
				try
				{
					dtbRet.Columns.Add(PROGRESSDAY + i.ToString("00"),typeof(System.Double));
				}
				catch{}
				try
				{
					dtbRet.Columns.Add(PROGRESS + i.ToString("00"),typeof(System.Double));
				}
				catch{}

				try
				{
					dtbRet.Columns.Add(ASSESSMENT + i.ToString("00"),typeof(System.String));
				}
				catch{}


			} 	// FILL the null column
			
			return dtbRet;		
		}

		
		/// <summary>
		/// Thachnn : 08/Nov/2005
		/// Browse the DataTable, get all value of Category, PartNo column, insert into ArraysList as CategoryValue#PartNoValue
		/// </summary>
		/// <param name="pdtb">DataTable to collect values</param>			
		/// <param name="pstrCategoryColName"></param>
		/// <param name="pstrPartNoColName"></param>
		/// <returns>ArrayList of object, collect CategoryValue#PartNoValue pairs from pdtb. Empty ArrayList if error or not found any row in pdtb.</returns>		
		private ArrayList GetCategory_PartNo_Model_ProductID_FromTable(DataTable pdtb, string pstrCategoryColName, string pstrPartNoColName, string pstrModelColName, string pstrProductID)
		{
			ArrayList arrRet = new ArrayList();
			try
			{
				foreach (DataRow drow in pdtb.Rows)
				{
					object objCategoryGet = drow[pstrCategoryColName];
					object objPartNoGet = drow[pstrPartNoColName];
					object objModelGet = drow[pstrModelColName];
					object objProductIDGet = drow[pstrProductID];
					string str = objCategoryGet.ToString().Trim() + "#" + objPartNoGet.ToString().Trim() + "#" + objModelGet.ToString().Trim() + "#" + objProductIDGet.ToString().Trim();
					if( !arrRet.Contains(str)  )
					{
						arrRet.Add(str);
					}
				}
			}
			catch
			{
				arrRet.Clear();
			}
			return arrRet;
		}

	

		/// <summary>
		/// Thachnn: 10/11/2005
		/// execute the input sql clause
		/// return the object result
		/// throw all exception to outside
		/// </summary>
		/// <param name="pstrSql">SQL clause to execute</param>
		/// <returns>object</returns>
		public object ExecuteScalar(string pstrSql)
		{
			
			const string METHOD_NAME = THIS + ".ExecuteScalar()";
			
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
		
			string strSql = pstrSql;				

			
			oconPCS = new OleDbConnection(mConnectionString);
			ocmdPCS = new OleDbCommand(strSql, oconPCS);

			ocmdPCS.Connection.Open();
			return ocmdPCS.ExecuteScalar();				
		
			if (oconPCS!=null) 
			{
				if (oconPCS.State != ConnectionState.Closed) 
				{
					oconPCS.Close();
				}
			}			
		}

	



		/// <summary>
		/// <author>Thachnn</author>
		/// Get the datatable : contain Mapping ProductID with it adjustment from rev1 to rev2, in a specific days in month)
		/// Return table will have schema like:
		/// ProductID - AdjDay - AdjQuantity
		/// 200	1	110.00000
		/// 127	13	53.00000
		/// 127	31	50.00000
		/// 
		/// 				
		/// </summary>
		/// <param name="pstrCCNID"></param>
		/// <param name="pstrYear"></param>
		/// <param name="pstrMonth"></param>
		/// <param name="pstrPartyID"></param>
		/// <param name="pstrPurchaseOrderMasterID_1"></param>
		/// <param name="pstrPurchaseOrderMasterID_2"></param>		
		/// <returns></returns>
		private DataTable BuildAdjTable(DataTable pdtbPlanTableWO1, DataTable pdtbPlanTableWO2 )
		{	
            /// TABLE1 - TABLE2

			/// build schema for ADJ table
			const string ADJ_TABLE_NAME = "AdjTable";
			DataTable dtbRet = new DataTable(ADJ_TABLE_NAME);
			dtbRet.Columns.Add(PRODUCTID);
			dtbRet.Columns.Add(ADJ + DATE, typeof(Int32) );
			dtbRet.Columns.Add(ADJ + QUANTITY, typeof(Decimal) );			
			
			// HACKED: improved Speed
			//DataTable dtbPlanTableWO1 = BuildPlanTable(pstrCCNID,pstrYear,pstrMonth,pstrPartyID, pstrPurchaseOrderMasterID_1);
			DataTable dtbPlanTableWO1 = pdtbPlanTableWO1;
			/* DataTable dtbPlanTableWO2 = BuildPlanTable(pstrCCNID,pstrYear,pstrMonth,pstrPartyID, pstrPurchaseOrderMasterID_2); */
			DataTable dtbPlanTableWO2 = pdtbPlanTableWO2;

			
			/// FOREACH iROW IN TABLE2, 
			/// dtbRet newROW = iROW
			/// if found relative-newROW    row in Table1 (productid is the same, planday is the same), 
			/// ---- subtract PlanQuantity of current row (newROW).
			/// ---- Add found row in Table1 in the UsedRowedInTableWO1
			/// add newROW to dtbRET
			/// 
			/// Table1.Remove(UsedRowedInTableWO1);
			/// 
			/// FOREACH iROW1 in TABLE1
			/// add to the dtbRet

			/// REVIEW: need to review: sure that PlanTable is have unique key with PID - PlanDay
			
			if(pdtbPlanTableWO1.Rows.Count <= 0)
			{
				return dtbRet;
			}

			int nPID = int.MinValue;
			int nDay = int.MinValue;
			ArrayList arrUsedRowInPlanTableWO1 = new ArrayList();
			foreach(DataRow iRow in dtbPlanTableWO2.Rows)
			{
				DataRow newRow = dtbRet.NewRow();
				nPID = System.Convert.ToInt32(iRow[PRODUCTID]);				
				nDay = System.Convert.ToInt32(iRow[PLAN + DATE]);
				decimal dblAQ = Convert.ToDecimal( iRow[PLAN + QUANTITY]  );

				decimal dblRelativeFromTable1 = 0;
				foreach( DataRow jRow in dtbPlanTableWO1.Select("["+PRODUCTID+"]=" +nPID+ " and [" +PLAN+DATE+ "]=" +nDay)   )
				{
					dblRelativeFromTable1 += Convert.ToDecimal( jRow[PLAN + QUANTITY]) ;
					arrUsedRowInPlanTableWO1.Add(jRow);	/// mark that we used this rows. We don't include its value later.
				}

				newRow[PRODUCTID] = nPID;
				newRow[ADJ + DATE] = nDay;
				newRow[ADJ + QUANTITY] = dblAQ - dblRelativeFromTable1;
				dtbRet.Rows.Add(newRow);
			}

			foreach(DataRow jRow in arrUsedRowInPlanTableWO1)			
				dtbPlanTableWO1.Rows.Remove(jRow);
			
            
			foreach(DataRow iRow in dtbPlanTableWO1.Rows)
			{
                DataRow newRow = dtbRet.NewRow();
				newRow[PRODUCTID] = iRow[PRODUCTID];
				newRow[ADJ + DATE] = iRow[PLAN + DATE];
				try
				{
					
					newRow[ADJ + QUANTITY] = decimal.Negate( (decimal)iRow[PLAN + QUANTITY] );
				}
				catch{}				

				dtbRet.Rows.Add(newRow);
			}

			return dtbRet;
		}



		/// <summary>
		/// Get all data for this report and cache in the dstPLPP dataset
		/// just improve the speed for this report
		/// </summary>
		/// <param name="pstrCCNID"></param>
		/// <param name="pstrYear"></param>
		/// <param name="pstrMonth"></param>
		/// <param name="pstrPartyID"></param>
		/// <param name="pstrPurchaseOrderMasterID_1"></param>
		/// <param name="pstrPurchaseOrderMasterID_2"></param>
		/// <param name="pstrProportionStandard"></param>
		private void GetDataAndCache(string pstrCCNID, string pstrYear, string pstrMonth, string pstrPartyID, string pstrPORevision_1, string pstrPORevision_2, string pstrProportionStandard)
		{	
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;

			string pstrPreviousYear = pstrYear;
			string pstrPreviousMonth = pstrMonth;

			if(pstrMonth == "1" || pstrMonth == "01" )
			{
				pstrPreviousMonth = "12";
				pstrPreviousYear = (int.Parse(pstrYear) - 1).ToString();
			}
			else
			{
				pstrPreviousMonth = (int.Parse(pstrMonth) - 1).ToString();
			}



			#region MAIN SQL QUERY
				
			string strSql = 
				" Declare @pstrCCNID int " + 
				" Declare @pstrMonth char(2) " + 
				" Declare @pstrYear char(4) " + 
				" Declare @pstrPreviousMonth char(2) " + 
				" Declare @pstrPreviousYear char(4) " + 

				" Declare @pstrPartyID int " + 
				" Declare @pstrPORevision_1 int " + 			
				" Declare @pstrPORevision_2 int " + 			
				"  " + 				
				" /*-----------------------------------*/ " + 
				"  " + 
				" Set @pstrCCNID = " + pstrCCNID + " " + 
				" Set @pstrYear = '" + pstrYear + "' " + 
				" Set @pstrMonth = '"+ pstrMonth +"' " + 
				" Set @pstrPreviousYear = '" + pstrPreviousYear + "' " + 
				" Set @pstrPreviousMonth = '"+ pstrPreviousMonth +"' " + 

				" Set @pstrPartyID = " +pstrPartyID+ " " + 
				(pstrPORevision_1.Trim() == string.Empty ?  (string.Empty) : (" Set @pstrPORevision_1 = " +pstrPORevision_1 + " ")  )  + 			
				" Set @pstrPORevision_2 = " +pstrPORevision_2 + " "  + 			
				"  " + 					
				"  " ;
/*-----------------------------------*/


			#endregion MAIN QUERY


			#region PLANTABLE - 2 - MAIN

			/// Get the datatable : contain Mapping ProductID with it Plan from WO2, in a specific days in month)
			/// Return table will have schema like:
			/// ProductID - PlanDay - PlanQuantity
			/// 200	1	110.00000
			/// 200	2	10.00000
			/// 200	3	10.00000
			/// 200	4	40.00000
			/// 200	5	15.00000
			/// 127	7	50.00000
			/// 127	8	47.50000
			/// 127	9	52.50000
			/// 127	12	46.50000
			/// 127	13	53.00000
			/// 127	31	50.00000		
			/// 
			/// //private DataTable BuildPlanTable(string pstrCCNID, string pstrYear, string pstrMonth, string pstrPartyID, string pstrPurchaseOrderMasterID)
			string strSqlPLANTABLE =				 

				

/*END  ******************** PLAN  , IS PO_PurchaseOrder ***************************************************/
 " SELECT  " + 
 " ITM_Product.ProductID as [ProductID], " + 
 " ITM_Category.Code as [Category],  " + 
 " ITM_Product.Code as [Part No.],  " + 
 " ITM_Product.Description as [PartName],  " + 
 " ITM_Product.Revision  as [Model], " + 
 " IsNull(PROGRESSBEGINQUANTITYTABLE.ProgressBeginQuantity,0.00) as [ProgressBeginQuantity], " + 
 " PLANTABLE.PlanDay, " + 
 " PLANTABLE.PlanQuantity " + 
 "  " + 
 " FROM " + 
 " (		 " + 
 " 	SELECT  " + 
 " 	INNERTABLE.ProductID, " + 
 " 	INNERTABLE.PlanDay, " + 
 " 	CASE " + 
 " 		WHEN RQty < OQty THEN RQty " + 
 " 		ELSE OQty " + 
 " 	END as [PlanQuantity] " + 
 " 	 " + 
 " 	FROM  " + 
 " 	( " + 
 " 		SELECT  " + 
 " 		UNIONTABLE.ProductID, " + 
 " 		UNIONTABLE.PlanDay, " + 
 " 		Sum(UNIONTABLE.OQty) as [OQty], " + 
 " 		Sum(UNIONTABLE.RQty) as [RQty] " + 
 " 		FROM " + 
 " 		( /* Inner table: union of OrderQuantityTable and ReceivedQuantityTable */ " + 
 " 			(	 " + 
 " 				select  " + 
 " 				PODETAIL.ProductID as [ProductID], " + 
 " 				DatePart(dd, PODELI.ScheduleDate) as [PlanDay], " + 
 " 				SUM(PODELI.DeliveryQuantity) as [OQty], " + 
 " 				null as [RQty] " + 
 " 				 " + 
 " 				from  " + 
 " 				PO_PurchaseOrderMaster as POMASTER " + 
 " 				join PO_PurchaseOrderDetail as PODETAIL " + 
 " 				on POMASTER.PurchaseOrderMasterID = PODETAIL.PurchaseOrderMasterID " + 
 " 				and POMASTER.PORevision = @pstrPORevision_2  " + 
 " 				and POMASTER.CCNID = @pstrCCNID " + 
 " 				and POMASTER.PartyID = @pstrPartyID " + 
 " 				and PODETAIL.ApproverID is not null /* APPROVED */ " + 
 " 			 " + 
 " 				join PO_DeliverySchedule as PODELI " + 
 " 				on PODETAIL.PurchaseOrderDetailID = PODELI.PurchaseOrderDetailID " + 
 " 				and DatePart(mm   , PODELI.ScheduleDate) = @pstrMonth " + 
 " 				and DatePart(yyyy , PODELI.ScheduleDate) = @pstrYear " + 
 " 				 " + 
 " 				group by  " + 
 " 				PODETAIL.ProductID, " + 
 " 				DatePart(dd, PODELI.ScheduleDate) " + 
 " 			)  " + 
 " 		 " + 
 " 			UNION /* getting the ReceivedQuantity */  " + 
 " 			( " + 
 " 				select  " + 
 " 				PODETAIL.ProductID as [ProductID], " + 
 " 				DatePart(dd, PODELI.ScheduleDate) as [PlanDay], " + 
 " 				null as [OQty], " + 
 " 				Sum(PODELI.ReceivedQuantity) as [RQty] " + 
 " 				 " + 
 " 				from " + 
 " 				PO_PurchaseOrderMaster as POMASTER " + 
 " 				join PO_PurchaseOrderDetail as PODETAIL " + 
 " 				on POMASTER.PurchaseOrderMasterID = PODETAIL.PurchaseOrderMasterID " + 
 " 				and POMASTER.PORevision = @pstrPORevision_2  " + 
 " 				and POMASTER.CCNID = @pstrCCNID " + 
 " 				and POMASTER.PartyID = @pstrPartyID " + 
 " 				and PODETAIL.ApproverID is not null /* APPROVED */ " + 
 " 				and PODETAIL.Closed = 1 /* CLOSED */ " + 
 " 				 " + 
 " 				join PO_DeliverySchedule as PODELI " + 
 " 				on PODETAIL.PurchaseOrderDetailID = PODELI.PurchaseOrderDetailID " + 
 " 				and DatePart(mm   , PODELI.ScheduleDate) = @pstrMonth " + 
 " 				and DatePart(yyyy , PODELI.ScheduleDate) = @pstrYear " + 
 " 				and PODELI.ReceivedQuantity is not null /* RECEIVED */ " + 
 " 				 " + 
 " 				group by  " + 
 " 				PODETAIL.ProductID, " + 
 " 				DatePart(dd, PODELI.ScheduleDate) " + 
 " 			)			 " + 
 " 		 " + 
 " 		) as UNIONTABLE /*END: Inner table: union of OrderQuantityTable and ReceivedQuantityTable */ " + 
 " 		group by ProductID,  " + 
 " 				PlanDay " + 
 " 	) as INNERTABLE " + 
 "  " + 
 " ) as PLANTABLE " + 
 "  " + 
 "  " + 
 " /* Join to get Info data : Category Code, Product Code */ " + 
 " join ITM_Product " + 
 " on PLANTABLE.ProductID = ITM_Product.ProductID " + 
 " and ITM_Product.CCNID = @pstrCCNID " + 
 " LEFT join ITM_Category " + 
 " on ITM_Product.CategoryID = ITM_Category.CategoryID " + 
 "  " + 
 "  " + 
 " LEFT Join " + 
 " ( " + 
 " 	/* ============== PROGRESS BEGIN QUANTITY WITH Month = Parameter Month - 1 ================ */ " + 
 " 	SELECT  " + 
 " 	IsNull(PLANTABLE.ProductID,ACTUALTABLE.ProductID) as [ProductID], " + 
 " 	(ACTUALTABLE.ActualQuantity - PLANTABLE.PlanQuantity )    as ProgressBeginQuantity  " + 
 " 	 " + 
 " 	FROM " + 
 " 	(							 " + 
 " 		SELECT  " + 
 " 		INNERTABLE.ProductID, " + 
 " 		 " + 
 " 		CASE " + 
 " 			WHEN RQty < OQty THEN RQty " + 
 " 			ELSE OQty " + 
 " 		END as [PlanQuantity] " + 
 " 		 " + 
 " 		FROM  " + 
 " 		( " + 
 " 			SELECT  " + 
 " 			UNIONTABLE.ProductID, " + 
 " 			 " + 
 " 			Sum(UNIONTABLE.OQty) as [OQty], " + 
 " 			Sum(UNIONTABLE.RQty) as [RQty] " + 
 " 			FROM " + 
 " 			( /* Inner table: union of OrderQuantityTable and ReceivedQuantityTable */ " + 
 " 				(	 " + 
 " 					select  " + 
 " 					PODETAIL.ProductID as [ProductID], " + 
 " 					DatePart(dd, PODELI.ScheduleDate) as [PlanDay], " + 
 " 					SUM(PODELI.DeliveryQuantity) as [OQty], " + 
 " 					null as [RQty] " + 
 " 					 " + 
 " 					from  " + 
 " 					PO_PurchaseOrderMaster as POMASTER " + 
 " 					join PO_PurchaseOrderDetail as PODETAIL " + 
 " 					on POMASTER.PurchaseOrderMasterID = PODETAIL.PurchaseOrderMasterID " + 
 " 					and POMASTER.PORevision = @pstrPORevision_2  " + 
 " 					and POMASTER.CCNID = @pstrCCNID " + 
 " 					and POMASTER.PartyID = @pstrPartyID " + 
 " 					and PODETAIL.ApproverID is not null /* APPROVED */ " + 
 " 				 " + 
 " 					join PO_DeliverySchedule as PODELI " + 
 " 					on PODETAIL.PurchaseOrderDetailID = PODELI.PurchaseOrderDetailID " + 
 " 					and DatePart(mm   , PODELI.ScheduleDate) = @pstrPreviousMonth " + 
 " 					and DatePart(yyyy , PODELI.ScheduleDate) = @pstrPreviousYear " + 
 " 					 " + 
 " 					group by  " + 
 " 					PODETAIL.ProductID, " + 
 " 					DatePart(dd, PODELI.ScheduleDate) " + 
 " 				)  " + 
 " 			 " + 
 " 				UNION /* getting the ReceivedQuantity */  " + 
 " 				( " + 
 " 					select  " + 
 " 					PODETAIL.ProductID as [ProductID], " + 
 " 					DatePart(dd, PODELI.ScheduleDate) as [PlanDay], " + 
 " 					null as [OQty], " + 
 " 					Sum(PODELI.ReceivedQuantity) as [RQty] " + 
 " 					 " + 
 " 					from " + 
 " 					PO_PurchaseOrderMaster as POMASTER " + 
 " 					join PO_PurchaseOrderDetail as PODETAIL " + 
 " 					on POMASTER.PurchaseOrderMasterID = PODETAIL.PurchaseOrderMasterID " + 
 " 					and POMASTER.PORevision = @pstrPORevision_2  " + 
 " 					and POMASTER.CCNID = @pstrCCNID " + 
 " 					and POMASTER.PartyID = @pstrPartyID " + 
 " 					and PODETAIL.ApproverID is not null /* APPROVED */ " + 
 " 					and PODETAIL.Closed = 1 /* CLOSED */ " + 
 " 					 " + 
 " 					join PO_DeliverySchedule as PODELI " + 
 " 					on PODETAIL.PurchaseOrderDetailID = PODELI.PurchaseOrderDetailID " + 
 " 					and DatePart(mm   , PODELI.ScheduleDate) = @pstrPreviousMonth " + 
 " 					and DatePart(yyyy , PODELI.ScheduleDate) = @pstrPreviousYear " + 
 " 					and PODELI.ReceivedQuantity is not null /* RECEIVED */ " + 
 " 					 " + 
 " 					group by  " + 
 " 					PODETAIL.ProductID, " + 
 " 					DatePart(dd, PODELI.ScheduleDate) " + 
 " 				)			 " + 
 " 			 " + 
 " 			) as UNIONTABLE /*END: Inner table: union of OrderQuantityTable and ReceivedQuantityTable */ " + 
 " 			group by ProductID  " + 
 " 					 " + 
 " 		) as INNERTABLE " + 
 " 		 " + 
 " 	) as PLANTABLE " + 
 " 	 " + 
 " 	LEFT join  " + 
 " 	( " + 
 " 		/*BEGIN  ******************** ACTUAL QUANTITY, IS PO RECEIPT ***************************************************/ " + 
 " 		select  " + 
 " 		POREDETAIL.ProductID,  " + 
 " 		Sum(IsNull(POREDETAIL.ReceiveQuantity, 0.00)) as [ActualQuantity] " + 
 " 		 " + 
 " 		from PO_PurchaseOrderReceiptMaster as POREMASTER " + 
 " 		join PO_PurchaseOrderReceiptDEtail as POREDETAIL " + 
 " 		on POREMASTER.PurchaseOrderReceiptID = POREDETAIL.PurchaseOrderReceiptID " + 
 " 		and DATEPART(mm  , POREMASTER.PostDate) = @pstrPreviousMonth " + 
 " 		and DATEPART(yyyy, POREMASTER.PostDate) = @pstrPreviousYear " + 
 " 		and POREMASTER.CCNID = @pstrCCNID " + 
 " 		 " + 
 " 		join PO_PurchaseOrderMaster as POMASTER " + 
 " 		on POREDETAIL.PurchaseOrderMasterID = POMASTER.PurchaseOrderMasterID " + 
 " 		and POMASTER.PartyID = @pstrPartyID " + 
 " 		and POMASTER.PORevision = @pstrPORevision_2 " + 
 " 		 " + 
 " 		Group by  " + 
 " 		POREDETAIL.ProductID		 " + 
 " 		/*END  ******************** ACTUAL QUANTITY, IS PO RECEIPT ***************************************************/		 " + 
 " 	) as ACTUALTABLE " + 
 " 	 " + 
 " 	on PLANTABLE.ProductID = ACTUALTABLE.ProductID	 " + 
 " ) as PROGRESSBEGINQUANTITYTABLE " + 
 "  " + 
 " on PLANTABLE.ProductID = PROGRESSBEGINQUANTITYTABLE.ProductID " + 
/*END  ******************** PLAN  , IS PO_PurchaseOrder ***************************************************/


 "  " 	
				;

			#endregion PLANTABLE - MAIN
			/* ============================================================== */

			#region PLANTABLE - 1 - NOT MANDATORY			
			/// REV1 = SUm all order quantity of all POLine (of all PO of Provided Rev1). 			
			/// Get the datatable : contain Mapping ProductID with it Plan from Rev1, in a specific days in month)
			/// Return table will have schema like:
			/// ProductID - PlanDay - PlanQuantity
			/// 200	1	110.00000
			/// 200	2	10.00000			
			/// 127	7	50.00000
			/// 127	8	47.50000						
			string strSqlPLANTABLE_1 =			
			
/*START  ******************** PLAN  OF Revision 1, get Sum of Order Quantity of all POLine, ***************************************************/
 " SELECT   " + 
 " PODETAIL.ProductID as [ProductID], " + 
 " DatePart(dd, PODELI.ScheduleDate) as [PlanDay], " + 
 " SUM(PODELI.DeliveryQuantity) as [PlanQuantity] " + 
 "  " + 
 " from " + 
 " PO_PurchaseOrderMaster as POMASTER " + 
 " join PO_PurchaseOrderDetail as PODETAIL " + 
 " on POMASTER.PurchaseOrderMasterID = PODETAIL.PurchaseOrderMasterID " + 
 " and POMASTER.PORevision = @pstrPORevision_1  " + 
 " and POMASTER.CCNID = @pstrCCNID " + 
 " and POMASTER.PartyID = @pstrPartyID " + 
 "  " + 
 " join PO_DeliverySchedule as PODELI " + 
 " on PODETAIL.PurchaseOrderDetailID = PODELI.PurchaseOrderDetailID " + 
 " and DatePart(mm   , PODELI.ScheduleDate) = @pstrMonth " + 
 " and DatePart(yyyy , PODELI.ScheduleDate) = @pstrYear " + 
 "  " + 
 " group by  " + 
 " PODETAIL.ProductID, " + 
 " DatePart(dd, PODELI.ScheduleDate) "
/*END  ******************** PLAN  OF Revision 1, get Sum of Order Quantity of all POLine, ***************************************************/
				;

			#endregion PLANTABLE -1 NOT MANDATORY
			/* ============================================================== */

			#region ACTUALTABLE
			/// Get the datatable : contain Mapping ProductID with it Actual  PO RECEIPT quantity, in a specific days in month)
			/// Return table will have schema like:
			/// ProductID - ActualDay - ActualQuantity
			/// 200	1	110.00000
			/// 200	2	10.00000
			/// 200	3	10.00000
			/// 127	12	46.50000
			/// 127	13	53.00000
			/// 127	31	50.00000		
			/// </summary>
			/// <returns></returns>
			// private DataTable BuildActualTable(string pstrCCNID, string pstrYear, string pstrMonth, string pstrPartyID, string pstrPurchaseOrderMasterID_2)

			string strSqlACTUALTABLE =  
 " /*BEGIN  ******************** ACTUAL QUANTITY, IS PO RECEIPT ***************************************************/ " + 
 " select  " + 
 " POREDETAIL.ProductID,  " + 
 " DatePart(dd, POREMASTER.PostDate) as [ActualDay], " + 
 " Sum(IsNull(POREDETAIL.ReceiveQuantity, 0.00) ) as [ActualQuantity] " + 
 "  " + 
 " from PO_PurchaseOrderReceiptMaster as POREMASTER " + 
 " join PO_PurchaseOrderReceiptDEtail as POREDETAIL " + 
 " on POREMASTER.PurchaseOrderReceiptID = POREDETAIL.PurchaseOrderReceiptID " + 
 " and DATEPART(mm  , POREMASTER.PostDate) = @pstrMonth " + 
 " and DATEPART(yyyy, POREMASTER.PostDate) = @pstrYear " + 
 " and POREMASTER.CCNID = @pstrCCNID " +  
 "  " + 
 " join PO_PurchaseOrderMaster as POMASTER " + 
 " on POREDETAIL.PurchaseOrderMasterID = POMASTER.PurchaseOrderMasterID " + 
 " and POMASTER.PartyID = @pstrPartyID " + 
 " and POMASTER.PORevision = @pstrPORevision_2 " + 
 "  " + 
 " Group by  " + 
 " POREDETAIL.ProductID, " + 
 " DatePart(dd, POREMASTER.PostDate) " + 
 " /*END  ******************** ACTUAL QUANTITY, IS PO RECEIPT ***************************************************/ " + 
 "  " 				;


			#endregion ACTUALTABLE

			/* ============================================================== */
			

			#region RETURN TO VENDOR TABLE

				string strSqlRETURNTOVENDORTABLE = 

                    
/*BEGIN ********************RETURN TO VENDOR ***************************************************/

 " select  " + 
 " RETURNDETAIL.ProductID, " + 
 " DatePart(dd, RETURNMASTER.PostDate) as [ReturnDay], " + 
 " Sum(RETURNDETAIL.Quantity) as [ReturnQuantity] " + 
 "  " + 
 " from PO_ReturnToVendorMaster as RETURNMASTER " + 
 " join PO_ReturnToVendorDetail as RETURNDETAIL " + 
 " on RETURNMASTER.ReturnToVendorMasterID = RETURNDETAIL.ReturnToVendorMasterID  " + 
 " and DATEPART(mm  , RETURNMASTER.PostDate) = @pstrMonth " + 
 " and DATEPART(yyyy, RETURNMASTER.PostDate) = @pstrYear " + 
 " and RETURNMASTER.CCNID = @pstrCCNID " + 
 " and RETURNMASTER.PartyID = @pstrPartyID " + 
 "  " + 
 " join PO_PurchaseOrderMaster as POMASTER " + 
 " on RETURNMASTER.PurchaseOrderMasterID = POMASTER.PurchaseOrderMasterID " + 
 " and POMASTER.PORevision = @pstrPORevision_2 " +  
 "  " + 
 " Group by " + 
 " RETURNDETAIL.ProductID, " + 
 " DatePart(dd, RETURNMASTER.PostDate) " + 
/*END **********************RETURN TO VENDOR ***************************************************/

 "  " ;

			#endregion RETURN TO VENDOR TABLE	
			

			#region ADJTABLE



			#endregion ADJTABLE	
			


			try 
			{
				DataSet dstPCS = new DataSet();
				oconPCS = null;
				ocmdPCS = null;
			
				strSql += strSqlPLANTABLE + "\n" +  
					strSqlPLANTABLE_1 + "\n" + 
					strSqlACTUALTABLE + "\n" +
					strSqlRETURNTOVENDORTABLE + "\n"
					 ;
	

				
				oconPCS = new OleDbConnection(mConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				
				ocmdPCS.Connection.Open();				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPLPP);

				dstPLPP.Tables[0].TableName = PLAN_TABLE_NAME_2;
				dstPLPP.Tables[1].TableName = PLAN_TABLE_NAME_1;
				dstPLPP.Tables[2].TableName = ACTUAL_TABLE_NAME;
				dstPLPP.Tables[3].TableName = RETURN_TO_VENDOR_TABLE_NAME;
			}
			catch(OleDbException ex)
			{
				throw new Exception(strSql,ex);
			}			
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
			
		}



		/// <summary>
		/// This function return the previous PO Revision of provided Revision .
		/// If on Error, or there is no previous, it will return a negative value.
		/// </summary>
		/// <param name="pstrCCNID"></param>
		/// <param name="pstrYear"></param>
		/// <param name="pstrMonth"></param>
		/// <param name="pstrPartyID"></param>
		/// <param name="pstrPreviousPORevision"></param>
		/// <returns></returns>
		private int GetPreviousPORevision(string pstrCCNID, string pstrYear, string pstrMonth, 
			string pstrPartyID, string pstrCurrentPORevision)
		{
			const int NO_REV = -1;
			int intRet = NO_REV;
			
			#region DB QUERY
				
			string strSql = 
				" Declare @pstrCCNID int " + 
				" Declare @pstrMonth char(2) " + 
				" Declare @pstrYear char(4) " + 
				" Declare @pstrPartyID int " + 
				" /*-----------------------------------*/ " + 
				" Set @pstrCCNID = " +pstrCCNID+ " " + 
				" Set @pstrMonth = '" +pstrMonth+ "' " + 
				" Set @pstrYear = '" +pstrYear+ "' " + 				
				" Set @pstrPartyID = '" +pstrPartyID+ "' " + 
				" /*-----------------------------------*/ " + 
				"  " + 
			
 " select   " + 
 " IsNull( Max(PORevision) , -1) as MaxPORevision " + 
 " from    " + 
 " PO_PurchaseOrderMaster as POMASTER   				 " + 
 "  " + 
 " where POMASTER.CCNID = @pstrCCNID    " + 
 " and DatePart(mm   , POMASTER.OrderDate) = @pstrMonth    " + 
 " and DatePart(yyyy , POMASTER.OrderDate) = @pstrYear     " +  
 " and POMASTER.PartyID = @pstrPartyID " + 
 " and POMASTER.PORevision < " + pstrCurrentPORevision ;
			/*-----------------------------------*/

			try
			{
				intRet = Convert.ToInt32( ExecuteScalar(strSql) );
			}
			catch
			{}

			#endregion DB QUERY			

			return intRet;
		}	// end function




	}	// end class

}	// end namespace
